﻿using Corina.Datamodel.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Corina.Enumerations;
using System.Text.RegularExpressions;
using Corina.Datamodel.Base;

namespace Corina.DataContext.Service
{
    public class DeviceMessageService : ServiceBase
    {
        /// <summary>
        /// 获取所有设备消息
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="deviceSn">设备序列号</param>
        /// <param name="kindId">设备分类编号</param>
        /// <param name="key">关键字</param>
        /// <param name="searchAll">是否查询所有条目，为否表示仅查询未解析条目</param>
        /// <param name="index">索引</param>
        /// <param name="size">每页数量</param>
        /// <param name="count">总数</param>
        /// <returns></returns>
        public DataTable GetAllItems(string connectionString, string deviceSn, int kindId, string key, bool searchAll, int index, int size, ref int count)
        {
            StringBuilder builder = new StringBuilder();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            List<SqlParameter> paras = new List<SqlParameter>();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tdm.MeasureTime DESC) AS ROW_NUMBER,tdm.*,tdk.Name AS Kind,tu.Name AS UserName  ");
            builder.Append("FROM t_device_message AS tdm ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device AS td ON td.DeviceSn = tdm.DeviceSn ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device_kind AS tdk ON tdk.Id = td.DeviceKindId ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device_sale AS tds ON tds.DeviceSn = tdm.DeviceSn ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = tds.UserId ");
            builder.Append("WHERE 1=1 ");

            builder.Append("AND tdm.DeviceSn LIKE @DeviceSn ");
            paras.Add(new SqlParameter("@DeviceSn", string.Format("%{0}%", deviceSn)));

            builder.Append("AND tdm.[Content] LIKE @Key ");
            paras.Add(new SqlParameter("@Key", string.Format("%{0}%", key)));

            if (kindId > 0)
            {
                builder.Append("AND tdk.Id = @KindId ");
                paras.Add(new SqlParameter("@KindId", kindId));
            }
            if (!searchAll)
            {
                builder.Append("AND tdm.IsAnalysed = @IsAnalysed ");
                paras.Add(new SqlParameter("@IsAnalysed", false));
            }

            builder.Append(" ) AS QUERY ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;

        }

        public DataTable GetUncalibrateDevice(string connectionString)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT DISTINCT(tdm.ProofValue),td.DeviceSn, td.ManufactureDate, tdk.Name AS Kind,tddtt.Title AS DataModule,tu.Name AS UserName");
            builder.Append("  FROM t_device AS td ");
            builder.Append("LEFT JOIN t_device_kind AS tdk ON tdk.Id = td.DeviceKindId  ");
            builder.Append("LEFT JOIN t_device_data_transfer_type AS tddtt ON  tddtt.Id = td.DataTransferTypeId ");
            builder.Append("LEFT JOIN t_device_sale AS tds ON tds.DeviceSn = td.DeviceSn ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = tds.UserId ");
            builder.Append("LEFT JOIN CORINA_DATA.dbo.t_device_message AS tdm ON tdm.DeviceSn = td.DeviceSn ");
            builder.Append("WHERE td.DeviceSn IN ( ");
            builder.Append("SELECT tdm.DeviceSn FROM CORINA_DATA.dbo.t_device_message AS tdm WHERE len(tdm.ProofValue)>0 AND tdm.DeviceSn <> tdm.ProofValue) ");

            return DbOperator.GetDataTable(connectionString, builder.ToString());
        }

        public bool Update(string connectionString, DeviceMessageInfo entity, EntityOperation operation)
        {
            return DbOperator.UpdateEntity(connectionString, entity, operation);
        }

        public DeviceMessageInfo GetEntity(string connectionString, int id)
        {
            return DbOperator.GetEntity<DeviceMessageInfo>(connectionString, new List<string>() { "Id" }, new List<object>() { id });
        }

        public bool Resolve(string connectionString, int id)
        {
            DeviceMessageInfo deviceMessage = GetEntity(connectionString, id);
            if (deviceMessage == null || deviceMessage.IsAnalysed)
            {
                return false;
            }

            else
            {
                DeviceMeasureInfo measure = GetMeasure(deviceMessage.Content, id);
                StringBuilder builder = new StringBuilder();
                List<SqlParameter> paras = new List<SqlParameter>();

                builder.Append("INSERT INTO t_device_measure( Name, Density, Temperature, Humidity, IsOpen, Tester, TestTime, MessageId )");
                builder.Append("VALUES(@Name,@Density, @Temperature, @Humidity, @IsOpen, @Tester, @TestTime, @MessageId) ");

                paras.Add(new SqlParameter("@Name", measure.Name));
                paras.Add(new SqlParameter("@Density", measure.Density));
                paras.Add(new SqlParameter("@Temperature", measure.Temperature));
                paras.Add(new SqlParameter("@Humidity", measure.Humidity));
                paras.Add(new SqlParameter("@IsOpen", measure.IsOpen));
                paras.Add(new SqlParameter("@Tester", measure.Tester));
                paras.Add(new SqlParameter("@TestTime", measure.TestTime));
                paras.Add(new SqlParameter("@MessageId", measure.MessageId));

                string updateString = "UPDATE t_device_message SET IsAnalysed = @IsAnalysed WHERE Id=@MsgId";
                paras.Add(new SqlParameter("@IsAnalysed", true));
                paras.Add(new SqlParameter("@MsgId", id));

                return DbOperator.ExexuteCommand(connectionString, new List<string>() { builder.ToString(), updateString }, paras.ToArray()) > 0;
            }
        }

        private DeviceMeasureInfo GetMeasure(string content, int id)
        {
            DeviceMeasureInfo measure = new DeviceMeasureInfo() { MessageId = id, IsOpen = true };
            //   二价铜 000 PPM 温度:21 ℃ 湿度:36% 检测人:D 日期:2014-01-03 时间:13:36;
            measure.Name = Regex.Match(content, "   (.*?) ").Value.Trim();
            measure.Density = Convert.ToDouble(Regex.Match(Regex.Match(content, "[0-9]{3} PPM").Value, "[0-9]{3}").Value);
            measure.Temperature = Convert.ToDouble(Regex.Match(Regex.Match(content, "[0-9]{2} ℃").Value, "[0-9]{2}").Value);
            measure.Humidity = Convert.ToDouble(Regex.Match(Regex.Match(content, "[0-9]{2}%").Value, "[0-9]{2}").Value);
            measure.Tester = Regex.Match(content, "检测人:(.*?) ").Value.Substring(4).Trim();
            string timestring = string.Format("{0} {1}:00", Regex.Match(content, "[0-9]{4}-[0-9]{2}-[0-9]{2}").Value, Regex.Match(content, "[0-9]{2}:[0-9]{2}").Value);
            measure.TestTime = Convert.ToDateTime(timestring);

            return measure;
        }

        public bool Insert(string connectionString, IEnumerable<DeviceMessageInfo> entities, bool autoResulve)
        {
            List<DatamodelEntityBase> device_messages = new List<DatamodelEntityBase>();

            foreach (DeviceMessageInfo entity in entities)
            {
                //int _index = entity.Content.IndexOf("通道值");
                bool exist = CheckExist(connectionString, entity.DeviceSn, entity.Md5Code); //DbOperator.Exists(connectionString, "t_device_message", new List<string>() { "DeviceSn", "Content" }, new List<object>() { entity.DeviceSn, entity.Content });
                if (!exist)
                {
                    device_messages.Add(entity);
                }
            }
            if (device_messages.Count == 0)
            {
                return true;
            }
            if (!autoResulve)
            {
                return DbOperator.InsertEntities(connectionString, device_messages);
            }
            else
            {
                return InsertAndResolve(connectionString, device_messages);
            }
        }

        private bool InsertAndResolve(string connectionString, List<DatamodelEntityBase> deviceMessages)
        {
            int count = 0;
            deviceMessages.ForEach(t =>
            {
                //string queryString = t.SqlInsertString + " SELECT @@identity as Id";

                object messageId = DbOperator.InsertEntity(connectionString, t);

                if (messageId != null)
                {
                    int _messageId = Convert.ToInt32(messageId);

                    if (Resolve(connectionString, _messageId))
                    {
                        count++;
                    }
                }

            });

            return count > 0;
        }

        public bool CheckExist(string connectionString, string deviceSn, string md5Code)
        {
            string queryString = "SELECT COUNT(*) FROM t_device_message AS tdm WHERE tdm.DeviceSn = @DeviceSn AND tdm.Md5Code = @Md5Code AND tdm.Md5Code IS NOT NULL ";

            DataTable table = DbOperator.GetDataTable(connectionString, queryString, new SqlParameter("@DeviceSn", deviceSn), new SqlParameter("@Md5Code", md5Code));

            if (table == null || table.Rows.Count == 0)
            {
                return false;
            }
            return Convert.ToInt32(table.Rows[0][0]) > 0;
            //return DbOperator.GetCount(connectionString, queryString, new SqlParameter("@DeviceSn", deviceSn), new SqlParameter("@Md5Code", md5Code)) > 0;
        }
    }
}
